/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mpinventory;

import java.sql.*;
import java.util.Date;
import java.util.List;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author user
 */
public class TransactionReleaseQuery {
    private Connection conn;
    public TransactionRelease release=new TransactionRelease();
    public TransactionReleaseQuery(Connection conn){
        this.conn=conn;
    }
    public List<Installation> initializeInstallations(){
        List<Installation> installations=new ArrayList<Installation>();
        List<Integer> IDs=new ArrayList<Integer>();
        List<Integer> itemIDs=new ArrayList<Integer>();
        List<String> descriptions=new ArrayList<String>();
        List<Float> quantities=new ArrayList<Float>();
        List<String> units=new ArrayList<String>();
        List<Float> prices=new ArrayList<Float>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM airconinstallation NATURAL JOIN airconinstallationwe NATURAL JOIN airconinstallationdata NATURAL JOIN inventoryitemdata ORDER BY brandName ASC";
            ResultSet rs=stmt.executeQuery(query);
            int installationID=0;
            Installation installation=new Installation();
            int i=0;
            while(rs.next()){
                if(installationID!=rs.getInt("installationID")){
                    if(i!=0){
                        installation.setIDs(IDs);
                        installation.setItemIDs(itemIDs);
                        installation.setItemNames(descriptions);
                        installation.setQuantities(quantities);
                        installation.setUnits(units);
                        installation.setPrices(prices);
                        installations.add(installation);
                    }
                    installation=new Installation();
                    installationID=rs.getInt("installationID");
                    installation.setInstallationID(installationID);
                    installation.setBrand(rs.getString("brandName"));
                    installation.setModel(rs.getString("brandModel"));
                    installation.setDescription(rs.getString("brandDescription"));
                    installation.setHorsePower(rs.getFloat("horsepower"));
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("installationQuantity"));
                    units.add(rs.getString("installationUnit"));
                    prices.add(rs.getFloat("orderPrice"));
                    i=1;
                }
                else{
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("installationQuantity"));
                    units.add(rs.getString("installationUnit"));
                    prices.add(rs.getFloat("orderPrice"));
                }
            }
            installation.setIDs(IDs);
            installation.setItemIDs(itemIDs);
            installation.setItemNames(descriptions);
            installation.setQuantities(quantities);
            installation.setUnits(units);
            installation.setPrices(prices);
            installations.add(installation);
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return installations;
    }
    public void addRelease(int orderID, int customerID, String customerName, Date orderDate, String orderStatus, List<String> serialNumber, List<Integer> orderQuantity, List<Float> orderPrice, List<Integer> installationID, List<String> brandName, List<String> brandModel){
        String query="insert into customerorderdata(orderID, customerID, customerName, orderDate, orderStatus) values(?,?,?,?,?)";
        java.sql.Date date=new java.sql.Date(orderDate.getTime());
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, orderID);
            prest.setInt(2, customerID);
            prest.setString(3, customerName);
            prest.setDate(4, date);
            prest.setString(5, orderStatus);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully added Release " + orderID + ": " + customerName + " to the database!");
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        query="insert into customerorder(serialNumber, orderQuantity, orderPrice, installationID, brandName, brandModel) values(?,?,?,?,?,?)";
        int i=0;
        while(!installationID.isEmpty()){
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setString(1, serialNumber.get(i));
                prest.setInt(2, orderQuantity.get(i));
                prest.setFloat(3, orderPrice.get(i));
                prest.setInt(4, installationID.get(i));
                prest.setString(5, brandName.get(i));
                prest.setString(6, brandModel.get(i));
                prest.executeUpdate();
                String query2="insert into customerorderwe(serialNumber, orderID) values(?,?)";
                try{
                    PreparedStatement prest2=conn.prepareStatement(query2);
                    prest2.setString(1, serialNumber.get(i));
                    prest2.setInt(2, orderID);
                    prest2.executeUpdate();
                }
                catch(SQLException ex){
                    Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
                serialNumber.remove(i);
                orderQuantity.remove(i);
                orderPrice.remove(i);
                installationID.remove(i);
                brandName.remove(i);
                brandModel.remove(i);
            }
            catch(SQLException ex){
                Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    public List<TransactionRelease> initializeReleases(){
        List<TransactionRelease> orders=new ArrayList<TransactionRelease>();
        List<String> serialNumbers=new ArrayList<String>();
        List<Integer> installationIDs=new ArrayList<Integer>();
        List<String> brandNames=new ArrayList<String>();
        List<String> brandModels=new ArrayList<String>();
        List<Integer> quantities=new ArrayList<Integer>();
        List<Float> horsePowers=new ArrayList<Float>();
        List<Float> prices=new ArrayList<Float>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM customerorder NATURAL JOIN customerorderwe NATURAL JOIN customerorderdata NATURAL JOIN airconinstallationdata NATURAL JOIN customerdata ORDER BY orderID DESC";
            ResultSet rs=stmt.executeQuery(query);
            int orderID=0;
            TransactionRelease order=new TransactionRelease();
            int i=0;
            while(rs.next()){
                if(orderID!=rs.getInt("orderID")){
                    if(i!=0){
                        order.setSerialNumbers(serialNumbers);
                        order.setInstallationIDs(installationIDs);
                        order.setBrands(brandNames);
                        order.setModels(brandModels);
                        order.setQuantities(quantities);
                        order.setHorsePowers(horsePowers);
                        order.setPrices(prices);
                        orders.add(order);
                    }
                    order=new TransactionRelease();
                    orderID=rs.getInt("orderID");
                    order.setOrderID(orderID);
                    order.setCustomerID(rs.getInt("customerID"));
                    order.setCustomerName(rs.getString("customerName"));
                    order.setDate(rs.getDate("orderDate"));
                    order.setStatus(rs.getString("orderStatus"));
                    serialNumbers.add(rs.getString("serialNumber"));
                    quantities.add(rs.getInt("orderQuantity"));
                    horsePowers.add(rs.getFloat("horsepower"));
                    prices.add(rs.getFloat("orderPrice"));
                    installationIDs.add(rs.getInt("installationID"));
                    brandNames.add(rs.getString("brandName"));
                    brandModels.add(rs.getString("brandModel"));
                    i=1;
                }
                else{
                    serialNumbers.add(rs.getString("serialNumber"));
                    quantities.add(rs.getInt("orderQuantity"));
                    horsePowers.add(rs.getFloat("horsepower"));
                    prices.add(rs.getFloat("orderPrice"));
                    installationIDs.add(rs.getInt("installationID"));
                    brandNames.add(rs.getString("brandName"));
                    brandModels.add(rs.getString("brandModel"));
                }
            }
            order.setSerialNumbers(serialNumbers);
            order.setInstallationIDs(installationIDs);
            order.setBrands(brandNames);
            order.setModels(brandModels);
            order.setQuantities(quantities);
            order.setHorsePowers(horsePowers);
            order.setPrices(prices);
            orders.add(order);
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return orders;
    }
    public void editRelease(int orderID, int customerID, String customerName, Date orderDate, String orderStatus, List<String> serialNumber, List<Integer> orderQuantity, List<Float> orderPrice, List<Integer> installationID, List<String> brandName, List<String> brandModel, List<String> origSerialNumber){
        String query="delete from customerorderwe where serialNumber=? AND orderID=?";
        int i=0;
        if(orderStatus.equals("Delivered"))
            updateInventory(installationID,orderQuantity);
        while(i!=origSerialNumber.size())
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setString(1, origSerialNumber.get(i));
                prest.setInt(2, orderID);
                prest.executeUpdate();
                i++;
            }
            catch(SQLException ex){
                Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        query="delete from customerorder where serialNumber=?";
        i=0;
        while(i!=origSerialNumber.size())
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setString(1, origSerialNumber.get(i));
                prest.executeUpdate();
                i++;
            }
            catch(SQLException ex){
                Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        
        query="update customerorderdata set customerID=?, customerName=?, orderDate=?, orderStatus=? where orderID=?";
        java.sql.Date date=new java.sql.Date(orderDate.getTime());
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, customerID);
            prest.setString(2, customerName);
            prest.setDate(3, date);
            prest.setString(4, orderStatus);
            prest.setInt(5, orderID);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully edited Release " + orderID + ": " + customerName + "!");
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        query="insert into customerorder(serialNumber, orderQuantity, orderPrice, installationID, brandName, brandModel) values(?,?,?,?,?,?)";
        i=0;
        while(!installationID.isEmpty()){
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setString(1, serialNumber.get(i));
                prest.setInt(2, orderQuantity.get(i));
                prest.setFloat(3, orderPrice.get(i));
                prest.setInt(4, installationID.get(i));
                prest.setString(5, brandName.get(i));
                prest.setString(6, brandModel.get(i));
                prest.executeUpdate();
                String query2="insert into customerorderwe(serialNumber, orderID) values(?,?)";
                try{
                    PreparedStatement prest2=conn.prepareStatement(query2);
                    prest2.setString(1, serialNumber.get(i));
                    prest2.setInt(2, orderID);
                    prest2.executeUpdate();
                }
                catch(SQLException ex){
                    Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
                serialNumber.remove(i);
                orderQuantity.remove(i);
                orderPrice.remove(i);
                installationID.remove(i);
                brandName.remove(i);
                brandModel.remove(i);
            }
            catch(SQLException ex){
                Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    public TransactionRelease search(int orderID){
        TransactionRelease order=new TransactionRelease();
        List<String> serialNumbers=new ArrayList<String>();
        List<Integer> installationIDs=new ArrayList<Integer>();
        List<String> brandNames=new ArrayList<String>();
        List<String> brandModels=new ArrayList<String>();
        List<Integer> quantities=new ArrayList<Integer>();
        List<Float> horsePowers=new ArrayList<Float>();
        List<Float> prices=new ArrayList<Float>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM customerorder NATURAL JOIN customerorderwe NATURAL JOIN customerorderdata NATURAL JOIN airconinstallationdata NATURAL JOIN customerdata WHERE orderID=?";
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, orderID);
            ResultSet rs=prest.executeQuery();
            while(rs.next()){
                order.setOrderID(rs.getInt("orderID"));
                order.setCustomerID(rs.getInt("customerID"));
                order.setCustomerName(rs.getString("customerName"));
                order.setDate(rs.getDate("orderDate"));
                order.setStatus(rs.getString("orderStatus"));
                serialNumbers.add(rs.getString("serialNumber"));
                quantities.add(rs.getInt("orderQuantity"));
                horsePowers.add(rs.getFloat("horsepower"));
                prices.add(rs.getFloat("orderPrice"));
                installationIDs.add(rs.getInt("installationID"));
                brandNames.add(rs.getString("brandName"));
                brandModels.add(rs.getString("brandModel"));
            }
            order.setSerialNumbers(serialNumbers);
            order.setInstallationIDs(installationIDs);
            order.setBrands(brandNames);
            order.setModels(brandModels);
            order.setQuantities(quantities);
            order.setHorsePowers(horsePowers);
            order.setPrices(prices);
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return order;
    }
    public List<TransactionRelease> search(String status){
        List<TransactionRelease> orders=new ArrayList<TransactionRelease>();
        List<String> serialNumbers=new ArrayList<String>();
        List<Integer> installationIDs=new ArrayList<Integer>();
        List<String> brandNames=new ArrayList<String>();
        List<String> brandModels=new ArrayList<String>();
        List<Integer> quantities=new ArrayList<Integer>();
        List<Float> horsePowers=new ArrayList<Float>();
        List<Float> prices=new ArrayList<Float>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM customerorder NATURAL JOIN customerorderwe NATURAL JOIN customerorderdata NATURAL JOIN airconinstallationdata NATURAL JOIN customerdata WHERE orderStatus=? ORDER BY orderID DESC";
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, status);
            ResultSet rs=prest.executeQuery();
            int orderID=0;
            TransactionRelease order=new TransactionRelease();
            int i=0;
            while(rs.next()){
                if(orderID!=rs.getInt("orderID")){
                    if(i!=0){
                        order.setSerialNumbers(serialNumbers);
                        order.setInstallationIDs(installationIDs);
                        order.setBrands(brandNames);
                        order.setModels(brandModels);
                        order.setQuantities(quantities);
                        order.setHorsePowers(horsePowers);
                        order.setPrices(prices);
                        orders.add(order);
                    }
                    order=new TransactionRelease();
                    orderID=rs.getInt("orderID");
                    order.setOrderID(orderID);
                    order.setCustomerID(rs.getInt("customerID"));
                    order.setCustomerName(rs.getString("customerName"));
                    order.setDate(rs.getDate("orderDate"));
                    order.setStatus(rs.getString("orderStatus"));
                    serialNumbers.add(rs.getString("serialNumber"));
                    quantities.add(rs.getInt("orderQuantity"));
                    horsePowers.add(rs.getFloat("horsepower"));
                    prices.add(rs.getFloat("orderPrice"));
                    installationIDs.add(rs.getInt("installationID"));
                    brandNames.add(rs.getString("brandName"));
                    brandModels.add(rs.getString("brandModel"));
                    i=1;
                }
                else{
                    serialNumbers.add(rs.getString("serialNumber"));
                    quantities.add(rs.getInt("orderQuantity"));
                    horsePowers.add(rs.getFloat("horsepower"));
                    prices.add(rs.getFloat("orderPrice"));
                    installationIDs.add(rs.getInt("installationID"));
                    brandNames.add(rs.getString("brandName"));
                    brandModels.add(rs.getString("brandModel"));
                }
            }
            order.setSerialNumbers(serialNumbers);
            order.setInstallationIDs(installationIDs);
            order.setBrands(brandNames);
            order.setModels(brandModels);
            order.setQuantities(quantities);
            order.setHorsePowers(horsePowers);
            order.setPrices(prices);
            orders.add(order);
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return orders;
    }
    public void updateInventory(List<Integer> installationID, List<Integer> orderQuantity){
        String query="select * from airconinstallation natural join airconinstallationwe natural join airconinstallationdata where installationID=?";
        for(int i=0;i<installationID.size();i++)
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, installationID.get(i));
            ResultSet rs=prest.executeQuery();
            String query2="update inventoryitemdata set itemQuantity=itemQuantity-?*? where itemID=?";
            while(rs.next()){
                try{
                    PreparedStatement prest2=conn.prepareStatement(query2);
                    prest2.setInt(1, orderQuantity.get(i));
                    prest2.setFloat(2, rs.getFloat("installationQuantity"));
                    prest2.setInt(3, rs.getInt("itemID"));
                    prest2.executeUpdate();
                }
                catch(SQLException ex){
                    Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public int setID(){
        try{
            Statement stmt=conn.createStatement();
            String query="select max(orderID) as maxID from customerorderdata";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next())
                return rs.getInt("maxID")+1;
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionReleaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
}
